package com.tjtt.tdm.util;

import java.io.File;
import java.io.IOException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFDateUtil;

import com.jacob.activeX.ActiveXComponent;
import com.jacob.com.ComThread;
import com.jacob.com.Dispatch;
import com.jacob.com.Variant;

public class ExcelTool {
	public ActiveXComponent applicationExcel = null;
	private String filePath = "";
	public Dispatch workBooks;
	public Dispatch workBook;
	public Dispatch currentSheet;
	
    public ExcelTool(String _filePath)
	{
		filePath = _filePath;
		if (applicationExcel == null||applicationExcel.m_pDispatch==0) {
			applicationExcel = new ActiveXComponent("Excel.Application");
	  	}
		if (workBooks == null||workBooks.m_pDispatch==0) {
			workBooks = applicationExcel.getProperty("Workbooks").toDispatch();
		}
		workBook = Dispatch.invoke(workBooks, "Open", Dispatch.Method, new Object[]{filePath,new Variant(true)},new int[1]).toDispatch();
		currentSheet = Dispatch.call(workBook, "Sheets",1).toDispatch();
	}
    
    public int GetSheetCount(){
    	Dispatch sheets = Dispatch.call(workBook,"Sheets").toDispatch();
    	int sheetCount = Dispatch.call(sheets,"count").toInt();
    	return sheetCount;
    }

    public void SetCurrentSheet(int sheetIndex){
    	currentSheet = Dispatch.call(workBook, "Sheets",sheetIndex).toDispatch();
    }
    
    public String GetCurrentSheetName(){
    	String sheetname = Dispatch.get(currentSheet,"Name").toString();
    	return sheetname;
    }
    
    public int GetSheetPageCount(int row,int col,int pagesize){
    	int pagecount = 1;
    	while(true){
    		int nextrow = pagecount*pagesize;
    		String text = GetCellText(row+nextrow,col);
    		if (text==null || "".equals(text)){
    			break;
    		}
    		else{
    			pagecount++;
    		}
    	}
    	return pagecount;
    }
    
    public int SetSheetPageInfo(int row,int col,int pagesize,int currentIndex,int pagecount){
    	if (pagesize == -1){
    		SetCells(row,col,"共 "+pagecount+" 页 第 "+currentIndex+" 页");
    		currentIndex++;
    		return currentIndex;
    	}
    	else{
    		int no = 0;
    		while(true){
        		int nextrow = no*pagesize;
        		no++;
        		String text = GetCellText(row+nextrow,col);
        		if (text==null || "".equals(text)){
        			break;
        		}
        		else{
        			SetCells(row+nextrow,col,"共 "+pagecount+" 页 第 "+currentIndex+" 页");
        			currentIndex++;
        		}
        	}
    		return currentIndex;
    	}
    }
    
    public void CopySheetAll(String sFile,String tFile,int tSheetIndex){
    	applicationExcel = new ActiveXComponent("Excel.Application");
    	workBooks = applicationExcel.getProperty("Workbooks").toDispatch();
    	Dispatch workBook1 = null;
    	Dispatch workBook2 = null;
		Dispatch sheet1 = null;
		Dispatch sheet2 = null;
    	try{
    	    workBook1 = Dispatch.invoke(workBooks, "Open", Dispatch.Method, new Object[]{sFile,new Variant(true)},new int[1]).toDispatch();
        	workBook2 = Dispatch.invoke(workBooks, "Open", Dispatch.Method, new Object[]{tFile,new Variant(true)},new int[1]).toDispatch();
        	Dispatch sheets = Dispatch.call(workBook1,"Sheets").toDispatch();
        	int sheetCount = Dispatch.call(sheets,"count").toInt();
        	Boolean isdeletexu = true;
        	for(int i=sheetCount;i>0;i--){
        		sheet1 = Dispatch.call(workBook1, "Sheets",i).toDispatch();
        		String sheet1name = Dispatch.get(sheet1,"Name").toString();
        		
        		if (sheet1name.equals("首页")){
        			sheet2 = Dispatch.call(workBook2, "Sheets",2).toDispatch();
        		}
        		else if (sheet1name.equals("检测要素示意图（续）")){
        			sheet2 = Dispatch.call(workBook2, "Sheets",3).toDispatch();
        			isdeletexu = false;
        		}
        		else if (sheet1name.equals("汇总")){
        			sheet2 = Dispatch.call(workBook2, "Sheets",4).toDispatch();
        		}
        		else if (sheet1name.equals("点位分布图")){
        			sheet2 = Dispatch.call(workBook2, "Sheets",5).toDispatch();
        		}
        		else if (sheet1name.equals("点位坐标")){
        			sheet2 = Dispatch.call(workBook2, "Sheets",6).toDispatch();
        		}
        		
//        		Dispatch workSheet = Dispatch.call(workBook2, "Sheets",tSheetIndex).toDispatch();
//            	Dispatch.call(Dispatch.get(workBook2, "sheets").toDispatch(), "add",workSheet).toDispatch();
//        	    sheet2 = Dispatch.call(workBook2, "Sheets",tSheetIndex).toDispatch();
//        	    if (sheet1name.equals("首页")){
//        	    	sheet1name = "检测坐标及检测要素示意图";
//        	    }
//        	    Dispatch.put(sheet2,"Name",sheet1name);
        	    
        	    Dispatch usedRange = Dispatch.call(sheet1, "UsedRange").toDispatch();
            	Dispatch cells = Dispatch.call(usedRange, "Cells").toDispatch();
            	Dispatch rows = Dispatch.call(cells, "Rows").toDispatch();
            	int rowCount = Integer.parseInt(Dispatch.call(rows, "Count").toString());
            	
            	usedRange = Dispatch.call(sheet1, "UsedRange").toDispatch();
            	cells = Dispatch.call(usedRange, "Cells").toDispatch();
            	rows = Dispatch.call(cells, "Columns").toDispatch();
            	int columnCount = Integer.parseInt(Dispatch.call(rows, "Count").toString());
        	    Dispatch rng1 = Dispatch.call(sheet1, "Range","A1",GetColumnName(columnCount)+rowCount).toDispatch();
            	Dispatch rng2 = Dispatch.call(sheet2,"Range","A1").toDispatch();
            	//Dispatch.call(rng1, "Copy",rng2);
            	Dispatch.call(rng1, "Copy");
            	Dispatch.call(rng2, "PasteSpecial",8);
            	Dispatch.call(rng1, "Copy",rng2);
        	}
        	if (isdeletexu){
            	for(int i=7;i>0;i--){
            		sheet2 = Dispatch.call(workBook2, "Sheets",i).toDispatch();
            		String sheet2name = Dispatch.get(sheet2,"Name").toString();
            		if (sheet2name.equals("检测坐标及检测要素示意图(续)"))
            		{
                    	Dispatch.put(sheet2, "visible",0);
            		}
            	}
        	}

            Dispatch.call(workBook2, "Save");
            Variant f = new Variant(false);
    		Dispatch.call(workBook2, "Close", f);
    		applicationExcel.invoke("Quit");
    	}
    	catch(Exception exp){
    		System.out.println(exp.toString());
    		Dispatch.call(workBook2, "Save");
            Variant f = new Variant(false);
    		Dispatch.call(workBook2, "Close", f);
    		applicationExcel.invoke("Quit");
    	}
    }
    
    public void CopySheet(String sFile,int sSheetIndex,String tFile,int tSheetIndex)
    {
    	applicationExcel = new ActiveXComponent("Excel.Application");
    	workBooks = applicationExcel.getProperty("Workbooks").toDispatch();
    	Dispatch workBook1 = null;
    	Dispatch workBook2 = null;
		Dispatch sheet1 = null;
		Dispatch sheet2 = null;
    	try{
    	    workBook1 = Dispatch.invoke(workBooks, "Open", Dispatch.Method, new Object[]{sFile,new Variant(true)},new int[1]).toDispatch();
        	workBook2 = Dispatch.invoke(workBooks, "Open", Dispatch.Method, new Object[]{tFile,new Variant(true)},new int[1]).toDispatch();
    		sheet1 = Dispatch.call(workBook1, "Sheets",sSheetIndex).toDispatch();
    		String sheet1name = Dispatch.get(sheet1,"Name").toString();
    		Dispatch workSheet = Dispatch.call(workBook2, "Sheets",tSheetIndex).toDispatch();
        	Dispatch.call(Dispatch.get(workBook2, "sheets").toDispatch(), "add",workSheet).toDispatch();
    	    sheet2 = Dispatch.call(workBook2, "Sheets",tSheetIndex).toDispatch();
    	    Dispatch.put(sheet2,"Name",sheet1name);
    	    
    	    Dispatch usedRange = Dispatch.call(sheet1, "UsedRange").toDispatch();
        	Dispatch cells = Dispatch.call(usedRange, "Cells").toDispatch();
        	Dispatch rows = Dispatch.call(cells, "Rows").toDispatch();
        	int rowCount = Integer.parseInt(Dispatch.call(rows, "Count").toString());
        	
        	usedRange = Dispatch.call(sheet1, "UsedRange").toDispatch();
        	cells = Dispatch.call(usedRange, "Cells").toDispatch();
        	rows = Dispatch.call(cells, "Columns").toDispatch();
        	int columnCount = Integer.parseInt(Dispatch.call(rows, "Count").toString());
    	    Dispatch rng1 = Dispatch.call(sheet1, "Range","A1",GetColumnName(columnCount)+rowCount).toDispatch();
        	Dispatch rng2 = Dispatch.call(sheet2,"Range","A1").toDispatch();
        	Dispatch.call(rng1, "Copy",rng2);

            Dispatch.call(workBook2, "Save");
            Variant f = new Variant(false);
    		Dispatch.call(workBook2, "Close", f);
    		applicationExcel.invoke("Quit");
    	}
    	catch(Exception exp){
    		System.out.println(exp.toString());
    		Dispatch.call(workBook2, "Save");
            Variant f = new Variant(false);
    		Dispatch.call(workBook2, "Close", f);
    		applicationExcel.invoke("Quit");
    	}
    }
    
    public String GetColumnName(int columnIndex){
    	String strColumns = "A,B,C,D,E,F,GH,I,J,K,L,M,N,O,P,Q,R,S,T,U,V,W,X,Y,Z";
    	String[] arrayColumn = strColumns.split(",");
    	return arrayColumn[columnIndex-1];
    }
    public String GetColumnNameLarge(int columnIndex){
    	String strColumns = "A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R,S,T,U,V,W,X,Y,Z" +
    			",AA,AB,AC,AD,AE,AF,AG,AH,AI,AJ,AK,AL,AM,AN,AO,AP,AQ,AR,AS,AT," +
    			"AU,AV,AW,AX,AY,AZ";
    	String[] arrayColumn = strColumns.split(",");
    	return arrayColumn[columnIndex-1];
    }
    
    public ExcelTool(String _filePath, boolean flag)
	{
    	if(flag){
    		ComThread.InitSTA();
    	}
		filePath = _filePath;
		applicationExcel = new ActiveXComponent("Excel.Application");
		workBooks = applicationExcel.getProperty("Workbooks").toDispatch();
		workBook = Dispatch.invoke(workBooks, "Open", Dispatch.Method, new Object[]{filePath,new Variant(true)},new int[1]).toDispatch();
		currentSheet = Dispatch.call(workBook, "Sheets",1).toDispatch();
	}
    
	public String GetCellText(int rowIndex,int columnIndex)
	{
		try{
			Dispatch rng = Dispatch.call(currentSheet,"Cells",rowIndex,columnIndex).toDispatch();
			return Dispatch.call(rng,"Text").toString();
		}
		catch(Exception exp)
		{
			Close();
			return "";
		}
	}
	
    public String GetCells(int sheetIndex,int rowIndex,int columnIndex)
	{
    	Dispatch workSheet = Dispatch.call(workBook, "Sheets",sheetIndex).toDispatch();
		Dispatch rng = Dispatch.call(workSheet,"Cells",rowIndex,columnIndex).toDispatch();
		return Dispatch.call(rng,"Value2").toString();
	}
    
    public String GetCellByName(int sheetIndex, String name)
	{
    	Dispatch workSheet = Dispatch.call(workBook, "Sheets",sheetIndex).toDispatch();
		try{
			Dispatch rng = Dispatch.call(workSheet,"Range",name).toDispatch();
			String s = Dispatch.call(rng,"Value2").toString();
			return s;
		}catch(Exception e){
			//e.printStackTrace();
			return null;
		}
	}
    
    //根据标签名称获得书签位置
    public int[] GetCellPositionByName(int sheetIndex, String name)
	{
    	Dispatch workSheet = Dispatch.call(workBook, "Sheets",sheetIndex).toDispatch();
		try{
			int[] position = new int[2];
			Dispatch rng = Dispatch.call(workSheet,"Range",name).toDispatch();
			if (rng==null){
				return null;
			}
			Dispatch name1 = Dispatch.call(rng,"Name").toDispatch();
			String name2 = Dispatch.call(name1,"Value").toString();
			String x = "";
			String y = "";
			int start1 = name2.indexOf("$")+1;
			int end1 = name2.lastIndexOf("$");
			int start2 = name2.lastIndexOf("$")+1;
			int end2 = name2.length();
			x = name2.substring(start2,end2);
			y = name2.substring(start1,end1);
			int yindex = -1;
			if (y.length()==1){
				yindex = LetterToInt(y);
			}
			else{
				String letter1 = y.substring(0,1);
				yindex = (LetterToInt(letter1))*26+LetterToInt(y.substring(y.length()-1,y.length()));
			}
			position[0] = Integer.parseInt(x);
			position[1] = yindex;
			return position;
		}catch(Exception e){
			//e.printStackTrace();
			return null;
		}
	}
    
    public void SetCellByName(String rangeName,String rangeValue){
		try{
			Dispatch rng = Dispatch.call(currentSheet,"Range",rangeName).toDispatch();
			Dispatch.put(rng,"Value2",rangeValue);
		}catch(Exception e){
			//e.printStackTrace();
		}
    }
    
    public void SetCellByName(int sheetIndex,String rangeName,String rangeValue){
    	Dispatch workSheet = Dispatch.call(workBook, "Sheets",sheetIndex).toDispatch();
		try{
			Dispatch rng = Dispatch.call(workSheet,"Range",rangeName).toDispatch();
			Dispatch.put(rng,"Value2",rangeValue);
		}catch(Exception e){
			//e.printStackTrace();
		}
    }
    
    /**
     * 获取单元格的标签名
     * @param sheetIndex
     * @param rowIndex
     * @param columnIndex
     * @return 没有标签则返回null
     */
    public String GetCellName(int sheetIndex,int rowIndex,int columnIndex)
	{
    	Dispatch workSheet = Dispatch.call(workBook, "Sheets",sheetIndex).toDispatch();
    	
		try{
			Dispatch rng = Dispatch.call(workSheet,"Cells",rowIndex,columnIndex).toDispatch();
			Dispatch rangeName = Dispatch.get(rng,"Name").toDispatch();
			String rangeName1 = Dispatch.get(rangeName,"Name").toString();
			return rangeName1;
		}catch(Exception e){
			//e.printStackTrace();
			return null;
		}
		
	}
    
    /**
     * 获取单元格标准名称，例如：A1，B2
     * @param rowIndex
     * @param columnIndex
     * @return
     */
    public String GetCellNameNormal(int rowIndex,int columnIndex)
	{
    	String rangeName=null;
    	if (columnIndex <= 256) {
            if (columnIndex <= 26) {
            	char temp = (char) (64 + columnIndex);
                rangeName = temp +"" + rowIndex;
            }else if (columnIndex % 26 == 0) {
            	char temp = (char) (63 + (columnIndex / 26));
                rangeName = temp +""+ 'Z' + rowIndex+"";
            }  else {
            	char temp1 = (char) (64 + (columnIndex / 26));
            	char temp2 = (char) (64 + (columnIndex % 26));
                rangeName = temp1 +""+ temp2 + rowIndex+"";
            }
        } else {
            rangeName = null;
        }
		return rangeName;
	}
    
    /**
     * 判断当前单元格由几个单元格行合并而成
     * @param sheetIndex
     * @param rowIndex
     * @param columnIndex
     * @return
     */
    public Integer GetCellMergeRows(int sheetIndex,int rowIndex,int columnIndex)
	{
    	Dispatch workSheet = Dispatch.call(workBook, "Sheets",sheetIndex).toDispatch();
    	//TANGER_OCX.ActiveDocument.Sheets("Sheet1").Range("B1").MergeArea.Rows.Count
		try{
			Dispatch cell = Dispatch.call(workSheet,"Cells",rowIndex,columnIndex).toDispatch();
			Dispatch mergeArea = Dispatch.call(cell,"MergeArea").toDispatch();
			Dispatch Rows = Dispatch.call(mergeArea,"Rows").toDispatch();
			String rows = Dispatch.call(Rows, "Count").toString();
			return Integer.parseInt(rows);
		}catch(Exception e){
			e.printStackTrace();
			return null;
		}
		
	}
    
    /**
     * 判断当前单元格由几个单元格列合并而成
     * @param sheetIndex
     * @param rowIndex
     * @param columnIndex
     * @return
     */
    public Integer GetCellMergeColumns(int sheetIndex,int rowIndex,int columnIndex)
	{
    	Dispatch workSheet = Dispatch.call(workBook, "Sheets",sheetIndex).toDispatch();
    	//TANGER_OCX.ActiveDocument.Sheets("Sheet1").Range("B1").MergeArea.Rows.Count
		try{
			Dispatch cell = Dispatch.call(workSheet,"Cells",rowIndex,columnIndex).toDispatch();
			Dispatch mergeArea = Dispatch.call(cell,"MergeArea").toDispatch();
			Dispatch Columns = Dispatch.call(mergeArea,"Columns").toDispatch();
			String columns = Dispatch.call(Columns, "Count").toString();
			return Integer.parseInt(columns);
		}catch(Exception e){
			e.printStackTrace();
			return null;
		}
		
	}
    
    /**
     * 合并单元格
     * @param begin
     * @param end
     */
    public void MergeCell(String begin,String end)
	{
		try{
			Dispatch rng = Dispatch.call(currentSheet, "Range",begin,end).toDispatch();
			Dispatch.call(rng,"Merge");
		}
		catch(Exception exp)
		{
			Close();
		}
	}
    
    /**
     * 获取标签所在的位置
     * @param name 标签名称
     * @return 标签位置"x,y"
     */
    public String GetCellPositionByNameBianli(String name)
	{
    	int x=0;
    	int y=0;
    	int rowCount = GetRowCount();
    	int columnCount = GetColumnCount();
    	boolean flag = true;
    	for(int i=1; i<=rowCount&&flag; i++){
			for(int j=1; j<=columnCount; j++){
				String cellName = GetCellName(1, i, j);
				if(null == cellName)
					continue;
				if(cellName.equals(name)){
					x = i;
					y = j;
					flag=false;
					break;
				}
			}
		}
    	if(x==0 && y==0){	//没找到标签
    		return null;
    	}else{
    		return x+","+y;
    	}
	}
    
    /**
     * 
     * @param sheetIndex
     * @param rowIndex
     * @param count 插入行的数量
     */
    public void InsertRows(int sheetIndex,int rowIndex, int count)
    {
    	Dispatch workSheet = Dispatch.call(workBook, "Sheets",sheetIndex).toDispatch();
    	Dispatch range = Dispatch.call(workSheet,"Rows",rowIndex).toDispatch();
    	
    	for (int i = 0; i < count; i++)
        {
    		Dispatch.call(range,"Insert",-4121);
        }
    }
    
    /**
     * 
     * @param sheetIndex
     * @param rowIndex
     * @param count 插入行的数量
     */
    public void InsertRowsAndCopy(int sheetIndex,int rowIndex, int count)
    {
    	Dispatch workSheet = Dispatch.call(workBook, "Sheets",sheetIndex).toDispatch();
    	Dispatch range = Dispatch.call(workSheet,"Rows",rowIndex).toDispatch();
    	Dispatch rangedown = Dispatch.call(workSheet,"Rows",rowIndex+1).toDispatch();
    	
    	for (int i = 0; i < count; i++)
        {
    		Dispatch.call(rangedown,"Insert",-4121);
    		range = Dispatch.call(workSheet,"Rows",rowIndex).toDispatch();
    		rangedown = Dispatch.call(workSheet,"Rows",rowIndex+1).toDispatch();
			Dispatch.call(range, "Copy",rangedown);	
        }
    }
    
    /**
     * 
     * @param sheetIndex
     * @param rowIndex
     * @param count 插入行的数量
     */
    public void InsertRangesAndCopy(int sheetIndex,int rowIndex, int count,int meargeCount)
    {
    	Dispatch workSheet = Dispatch.call(workBook, "Sheets",sheetIndex).toDispatch();
    	Dispatch range = Dispatch.call(workSheet,"Rows",rowIndex).toDispatch();
    	Dispatch rangedown = Dispatch.call(workSheet,"Rows",rowIndex+meargeCount).toDispatch();
    	int columnCOunt = this.GetColumnCount();
    	for (int i = 0; i < count; i++)
        {
    		for(int j=0;j<meargeCount;j++){
    			Dispatch.call(rangedown,"Insert",-4121);
    		}
    		int endIndex = rowIndex+meargeCount-1;
    		range = Dispatch.call(workSheet, "Range","A"+rowIndex,IntToLetter(endIndex,columnCOunt)).toDispatch();
    		int targetIndex = rowIndex+(i+1)*meargeCount;
    		int targetendIndex = rowIndex+(i+2)*meargeCount-1;
    		Dispatch parseRange = Dispatch.call(workSheet,"Range","A"+targetIndex,IntToLetter(targetendIndex,columnCOunt)).toDispatch();
			Dispatch.call(range, "Copy",parseRange);	
        }
    }
    
    public void DeleteRows(int sheetIndex,int rowIndex, int count)
    {
    	Dispatch workSheet = Dispatch.call(workBook, "Sheets",sheetIndex).toDispatch();
    	Dispatch range = Dispatch.call(workSheet,"Rows",rowIndex).toDispatch();
    	
    	for (int i = 0; i < count; i++)
        {
    		Dispatch.call(range,"Delete");
        }
    }
    
    public void DeleteRange(int sheetIndex,String begin,String end)
    {
    	Dispatch workSheet = Dispatch.call(workBook, "Sheets",sheetIndex).toDispatch();
    	Dispatch rng = Dispatch.call(workSheet, "Range",begin,end).toDispatch();
    	Dispatch.call(rng,"Delete");
    }
    
    public void CopyRows(String sFile, int sSheetIndex, int sBeginRow, int  sRowCount, String tFile, int tSheetIndex, int tBeginRow)
    {
    	applicationExcel = new ActiveXComponent("Excel.Application");
    	workBooks = applicationExcel.getProperty("Workbooks").toDispatch();
    	Dispatch workBook1 = Dispatch.invoke(workBooks, "Open", Dispatch.Method, new Object[]{sFile,new Variant(true)},new int[1]).toDispatch();
    	Dispatch workBook2 = Dispatch.invoke(workBooks, "Open", Dispatch.Method, new Object[]{tFile,new Variant(true)},new int[1]).toDispatch();
		Dispatch sheet1 = Dispatch.call(workBook1, "Sheets",sSheetIndex).toDispatch();
		Dispatch sheet2 = Dispatch.call(workBook2, "Sheets",tSheetIndex).toDispatch();
		
		for(int i=0;i<sRowCount;i++)
		{
			Dispatch range1 = Dispatch.call(sheet1, "Rows",sBeginRow+i).toDispatch();
			Dispatch range2 = Dispatch.call(sheet2, "Rows",tBeginRow+i).toDispatch();
			Dispatch.call(range1, "Copy",range2);	
		}

        Dispatch.call(workBook2, "Save");
        Variant f = new Variant(false);
		Dispatch.call(workBook1, "Close", f);
		Dispatch.call(workBook2, "Close", f);
		applicationExcel.invoke("Quit");
    }
    
    public void CopyRowsInOneFile(int sSheetIndex, int sBeginRow, int  sRowCount, int tSheetIndex, int tBeginRow)
    {
    	Dispatch sheet1 = Dispatch.call(workBook, "Sheets",sSheetIndex).toDispatch();
		Dispatch sheet2 = Dispatch.call(workBook, "Sheets",tSheetIndex).toDispatch();
		for(int i=0;i<sRowCount;i++)
		{
			Dispatch range1 = Dispatch.call(sheet1, "Rows",sBeginRow+i).toDispatch();
			Dispatch range2 = Dispatch.call(sheet2, "Rows",tBeginRow+i).toDispatch();
			Dispatch.call(range1, "Copy",range2);	
		}
    }
    
    public void CopyRange(String sFile, int sSheetIndex, String sBegin, String  sEnd, String tFile, int tSheetIndex, String tBegin)
    {
    	applicationExcel = new ActiveXComponent("Excel.Application");
    	workBooks = applicationExcel.getProperty("Workbooks").toDispatch();
    	Dispatch workBook1 = null;
    	Dispatch workBook2 = null;
		Dispatch sheet1 = null;
		Dispatch sheet2 = null;
    	try{
    	    workBook1 = Dispatch.invoke(workBooks, "Open", Dispatch.Method, new Object[]{sFile,new Variant(true)},new int[1]).toDispatch();
        	workBook2 = Dispatch.invoke(workBooks, "Open", Dispatch.Method, new Object[]{tFile,new Variant(true)},new int[1]).toDispatch();
    		sheet1 = Dispatch.call(workBook1, "Sheets",sSheetIndex).toDispatch();
    	    sheet2 = Dispatch.call(workBook2, "Sheets",tSheetIndex).toDispatch();
    		
    		int sBeginx = GetRowIndex(sBegin);
    		int sBeginy = GetColumnIndex(sBegin);
    		int sEndx = GetRowIndex(sEnd);
    		int sEndy = GetColumnIndex(sEnd);
    		int tBeginx = GetRowIndex(tBegin);
    		int tBeginy = GetColumnIndex(tBegin);
    		
    		for (int i=0;i<=sEndx-sBeginx;i++)
    		{
    			for (int j=0;j<=sEndy-sBeginy;j++)
    			{
    				Dispatch range1 = Dispatch.call(sheet1, "Cells",sBeginx+i,sBeginy+j).toDispatch();
    				Dispatch range2 = Dispatch.call(sheet2, "Cells",tBeginx+i,tBeginy+j).toDispatch();
    				Dispatch.call(range1, "Copy",range2);	
    			}
    		}
    		
            Dispatch.call(workBook2, "Save");
            Variant f = new Variant(false);
    		Dispatch.call(workBook1, "Close", f);
    		Dispatch.call(workBook2, "Close", f);
    		applicationExcel.invoke("Quit");
    	}
    	catch(Exception exp){
    		Dispatch.call(workBook2, "Save");
            Variant f = new Variant(false);
    		Dispatch.call(workBook1, "Close", f);
    		Dispatch.call(workBook2, "Close", f);
    		applicationExcel.invoke("Quit");
    	}
    }
    
    public int GetRowCount()
    {
    	//int n=xsheet.UsedRange.Cells.Rows.Count; 
    	Dispatch usedRange = Dispatch.call(currentSheet, "UsedRange").toDispatch();
    	Dispatch cells = Dispatch.call(usedRange, "Cells").toDispatch();
    	Dispatch rows = Dispatch.call(cells, "Rows").toDispatch();
    	return Integer.parseInt(Dispatch.call(rows, "Count").toString());
    }
    
    public int GetRowCount(int sheetIndex)
    {
    	Dispatch sheet = Dispatch.call(workBook, "Sheets",sheetIndex).toDispatch();
    	Dispatch usedRange = Dispatch.call(sheet, "UsedRange").toDispatch();
    	Dispatch cells = Dispatch.call(usedRange, "Cells").toDispatch();
    	Dispatch rows = Dispatch.call(cells, "Rows").toDispatch();
    	return Integer.parseInt(Dispatch.call(rows, "Count").toString());
    }
    
    public int GetColumnCount()
    {
    	//int n=xsheet.UsedRange.Cells.Rows.Count; 
    	Dispatch usedRange = Dispatch.call(currentSheet, "UsedRange").toDispatch();
    	Dispatch cells = Dispatch.call(usedRange, "Cells").toDispatch();
    	Dispatch columns = Dispatch.call(cells, "Columns").toDispatch();
    	return Integer.parseInt(Dispatch.call(columns, "Count").toString());
    }
    
    public int GetRowIndex(String address)
    {
    	String rowValue = "";
        char[] sb = address.toCharArray();
		
		for (int i=0;i<sb.length;i++)
		{
			if (Character.isDigit(sb[i]))
			{
				rowValue+=sb[i];
			}
		}
    	return Integer.parseInt(rowValue);
    }
    
    public int GetColumnIndex(String address)
    {
    	String rowValue = "";
        char[] sb = address.toCharArray();
		
		for (int i=0;i<sb.length;i++)
		{
			if (Character.isLetter(sb[i]))
			{
				rowValue+=sb[i];
			}
		}
    	return LetterToInt(rowValue);
    }
    
    public void SetCells(int rowIndex, int columnIndex, String text)
    {
    	Dispatch rng = Dispatch.call(currentSheet,"Cells",rowIndex,columnIndex).toDispatch();
    	Dispatch.put(rng,"Value2",text);
    }
    public void SetCellsText(int rowIndex, int columnIndex, String text)
    {    	
//    	Dispatch rng = Dispatch.call(currentSheet,"Cells",rowIndex,columnIndex).toDispatch();
//    	Dispatch.put(rng,"Text",text);
    }
    public void SetCellFont(int rowIndex, int columnIndex, String text)
    {
    	Dispatch rng = Dispatch.call(currentSheet,"Cells",rowIndex,columnIndex).toDispatch();
    	Dispatch font = Dispatch.get(rng, "Font").toDispatch();
    	Dispatch.put(font, "Name", "Courier New");
    	Dispatch.put(rng,"Value2",text);
    }
    
    public void SetCells(int sheetIndex, int rowIndex, int columnIndex, String text)
    {
    	Dispatch sheet = Dispatch.call(workBook, "Sheets",sheetIndex).toDispatch();
    	Dispatch rng = Dispatch.call(sheet,"Cells",rowIndex,columnIndex).toDispatch();
    	Dispatch.put(rng,"Value2",text);
    }
    
    public void SetCellColor(int rowIndex, int columnIndex, int colorIndex)
    {
    	Dispatch rng = Dispatch.call(currentSheet,"Cells",rowIndex,columnIndex).toDispatch();
    	Dispatch font = Dispatch.get(rng, "Font").toDispatch();
    	Dispatch.put(font, "ColorIndex", colorIndex);
    }
    
    public void SetCellBackcolor(int rowIndex, int columnIndex, int colorIndex)
    {
    	Dispatch rng = Dispatch.call(currentSheet,"Cells",rowIndex,columnIndex).toDispatch();
    	Dispatch font = Dispatch.get(rng, "Interior").toDispatch();
    	Dispatch.put(font, "ColorIndex", colorIndex);
    }
    
    public int LetterToInt(String letter)
    {
        int n = 0;
        if (letter.length() >= 2)
        {
            char c1 = letter.toCharArray()[0];
            char c2 = letter.toCharArray()[1];
            
            c1 = Character.toUpperCase(c1);
            c2 = Character.toUpperCase(c2);
            
            int i = Integer.parseInt(String.valueOf(c1)) - 64;
            int j = Integer.parseInt(String.valueOf(c2)) - 64;

            n = i * 26 + j;
        }

        if (letter.length() == 1)
        {
            char c1 = letter.toCharArray()[0];

            c1 = Character.toUpperCase(c1);
         
            n = (int)c1-64;
        }

        return n;
    }
    
    public String IntToLetter(int row, int col)
    {
    	String[] colName = {"A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L","M",  "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z","AA", "AB", "AC", "AD", "AE", "AF", "AG", "AH", "AI", "AJ", "AK", "AL","AM",  "AN", "AO", "AP", "AQ", "AR", "AS", "AT", "AU", "AV", "AW", "AX", "AY", "AZ"};

    	String rangeName = "";
    	if (col <= 256) {
            if (col <= 26) {
                rangeName = colName[col - 1] + row;
            } else if (col % 26 == 0) {
                rangeName = (char)(65 + (col / 26) - 2) + "Z" + row;
            } else {
                rangeName = (char)(65 + (col / 26) - 1) + ""+(char)(65 + (col % 26) - 1) + row;
            }
        } else {
            rangeName = "";
        }
    	return rangeName;
    }
    
    public void setRowHeight(int sheetIndex, int rowIndex, int columnIndex, double rowHeight) {
    	Dispatch sheet = Dispatch.call(workBook, "Sheets",sheetIndex).toDispatch();
		Dispatch rng = Dispatch.call(sheet, "Cells", rowIndex, columnIndex).toDispatch();
		if (rowHeight > 0) {
			Dispatch.put(rng, "RowHeight", new Variant(rowHeight));
		}
	}
    
    public void setColumnWidth(int sheetIndex, int rowIndex, int columnIndex, double columnWidth) {
    	Dispatch sheet = Dispatch.call(workBook, "Sheets",sheetIndex).toDispatch();
		Dispatch rng = Dispatch.call(sheet, "Cells", rowIndex, columnIndex).toDispatch();
		if (columnWidth > 0) {
			Dispatch.put(rng, "ColumnWidth", new Variant(columnWidth));
		}
	}
    
    public void CopyRanges(String startRange,String endRange,String text,String targetRange)
    {
    	Dispatch rng1 = Dispatch.call(currentSheet,"Range",startRange,endRange).toDispatch();
    	Dispatch rng2 = Dispatch.call(currentSheet,"Range",targetRange).toDispatch();
    	Dispatch.call(rng1, "Copy",rng2);
    }
    
    public void CopyRangesInOneFile(int sSheetIndex, String startRange,String endRange, int tSheetIndex ,String targetRange)
    {
    	Dispatch sheet1 = Dispatch.call(workBook, "Sheets",sSheetIndex).toDispatch();
		Dispatch sheet2 = Dispatch.call(workBook, "Sheets",tSheetIndex).toDispatch();
    	Dispatch rng1 = Dispatch.call(sheet1,"Range",startRange,endRange).toDispatch();
    	Dispatch rng2 = Dispatch.call(sheet2,"Range",targetRange).toDispatch();
    	Dispatch.call(rng1, "Copy", rng2);
    	//Dispatch.call(rng2, "PasteSpecial",-4104);
    	//Dispatch.call(rng2, "PasteSpecial",-4122);
    }
    
	public void Save()
	{
		Variant f = new Variant(false);
    	Dispatch.call(workBook, "Save");
		Dispatch.call(workBook, "Close", f);
		applicationExcel.invoke("Quit");
		ComThread.Release();
	}
	
	public void Save(boolean flag)
	{
		Variant f = new Variant(false);
    	Dispatch.call(workBook, "Save");
		Dispatch.call(workBook, "Close", f);
		applicationExcel.invoke("Quit");
		workBooks = null;
		workBook = null;
		applicationExcel = null;
		if(flag){
			ComThread.Release();
		}
	}
	
	public void SaveFile()
	{
		Save();
	}
	
    public void SaveAsFile(String path, String format)
    {
    	Variant f = new Variant(false);
		Dispatch.call(workBook, "SaveAs",path);
		Dispatch.call(workBook, "Close", f);
		applicationExcel.invoke("Quit");
		ComThread.Release();
    }
    
    public void saveAsPDF(String pdffile) {
    	Dispatch.invoke(currentSheet, "ExportAsFixedFormat", Dispatch.Method, new Object[] {
    	new Variant(0) ,pdffile, new Variant(0),
    	new Variant(true), new Variant(true), new Variant(1),
    	new Variant(3), new Variant(false), new Variant(false) }, new int[1]);

    	Variant f = new Variant(false);
    	Dispatch.call(workBook, "Close", f);
    	applicationExcel.invoke("Quit");
		ComThread.Release();
    }
    public void Close()
    {
    	Variant f = new Variant(false);
		Dispatch.call(workBook, "Close", f);
		applicationExcel.invoke("Quit");
		ComThread.Release();
    }
    
    public void Close(boolean flag){
    	Variant f = new Variant(false);
		Dispatch.call(workBook, "Close", f);
		applicationExcel.invoke("Quit", new Variant[] {});
		workBooks = null;
		workBook = null;
		applicationExcel = null;
		if(flag){
			ComThread.Release();
		}
    }
    
    public void setChart(int chartIndex,int targetIndex,String startRange,String endRange,String title,String xTitle,String yTitle){
    	Object obj = null;
    	Dispatch workSheet1 = Dispatch.call(workBook, "Sheets",1).toDispatch();
    	Dispatch chartObj = Dispatch.call(workSheet1, "ChartObjects",1).toDispatch();
    	Dispatch chart = Dispatch.call(chartObj, "Chart").toDispatch();
    	Dispatch workSheet2 = Dispatch.call(workBook, "Sheets",targetIndex).toDispatch();
    	Dispatch range = Dispatch.call(workSheet2,"Range",startRange,endRange).toDispatch();
    	Dispatch.call(chart, "ChartWizard", range,4,obj,2,1,1,"true", title, xTitle, yTitle);
    }
    
    /**
     * 删除所有批注
     */
    public void delComments(){
    	Dispatch comments = Dispatch.call(currentSheet, "Comments").toDispatch();
    	int commentcount = Dispatch.call(comments,"count").toInt();
    	for(int i=1;i<=commentcount;i++){
    		Dispatch commenttarget = Dispatch.call(currentSheet, "Comments",i).toDispatch();
    		Dispatch.call(commenttarget,"Delete");
    	}
    }
    
    public static void main(String[] args){
    	ExcelTool et = new ExcelTool("d:\\表7-4 拉伸试验更新.xlsx");
    	System.out.println(et.GetCells(1, 2, 13));
    	et.Save();
    	
    	SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");      	 
        String  str = sdf.format(HSSFDateUtil.getJavaDate(42291.0)).toString();
        System.out.println(str); 
    }
	
}
